﻿using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using order.DbConfig;
using order.Models;
using System.Data;

namespace order.Service.ServiceImp
{
    public class SysUserServiceIml : ISysUserService
    {
        private readonly AppDbContext _dbContext;
        public SysUserServiceIml(AppDbContext dbContext) {
            _dbContext = dbContext;
        }

        class A { 
        public int UserId { get; set; }
        public string  UserName { get; set; }
        public int  RoleId { get; set; }
         public string RoleName { get; set; }
        }
        public void FindA()
            
        {

            var name = new MySqlParameter()
            {
                ParameterName = "@username",
                DbType = DbType.String,
                Value = "张三"
            };
           /* var name = new SqlParameter
            {
                ParameterName = "@username",
                SqlDbType = SqlDbType.VarChar,
                Value = "张三",
            };*/
           

            // string sql = $"SELECT\u.id AS UserId,\u.`name` AS UserName ,\r.id as RoleId,\r.role_name AS RoleNameFROM\sys_user u,\sys_role r,\sys_user_role ur WHERE\u.id = ur.user_id \AND ur.role_id = r.id \AND u.`name` = '{"张三"}'";
            var a =  _dbContext.Database.SqlQueryRaw<A>($"SELECT u.id AS UserId,u.`name` AS UserName ,r.id as RoleId, r.role_name AS RoleName FROM sys_user u,sys_role r,sys_user_role ur WHERE u.id = ur.user_id AND ur.role_id = r.id AND u.`name` = '张三'").ToList();
         
           var b=_dbContext.Database.SqlQuery<A>($"SELECT u.id AS UserId,u.`name` AS UserName ,r.id as RoleId, r.role_name AS RoleName FROM sys_user u,sys_role r,sys_user_role ur WHERE u.id = ur.user_id AND ur.role_id = r.id AND u.`name` = {"张三"}").ToList();
            Console.WriteLine(a);
            Console.WriteLine(b);


            var c = _dbContext.Database.SqlQueryRaw<A>($"SELECT u.id AS UserId,u.`name` AS UserName ,r.id as RoleId, r.role_name AS RoleName FROM sys_user u,sys_role r,sys_user_role ur WHERE u.id = ur.user_id AND ur.role_id = r.id AND u.`name` = @username", name).ToList();
            Console.WriteLine(c);
        }

        void ISysUserService.AddSysUser(SysUser sysUser)
        {
            _dbContext.SysUsers.Add(sysUser);
           
            _dbContext.SaveChanges();
           
        }

        void ISysUserService.DeleteSysUser(int id)
        {
           
            var sysUser = _dbContext.SysUsers.SingleOrDefault(u => u.Id == id);
            _ = _dbContext.SysUsers.Remove(sysUser);
            _dbContext.SaveChanges();
        }

        SysUser? ISysUserService.GetSysUser(int id)
        {
            FindA();
            var x=_dbContext.SysUsers.SingleOrDefault(s=>s.Id == id); 

            /*var x = _dbContext.SysUsers.SingleO(s => s.Id == id);
            Console.WriteLine(x); */  
            return _dbContext.SysUsers.FirstOrDefault(u => u.Id == id);
        }

        List<SysUser> ISysUserService.GetSysUsers()
        {
            return _dbContext.SysUsers.ToList();
        }

        void ISysUserService.UpdateSysUser(SysUser sysUser)
        {
            _dbContext.SysUsers.Update(sysUser);
            

        }
    }
}
